package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.dto.output.PersonAndSysUser;
import com.b511.swpu.cementbackend.entity.SysUser;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface SysUserRepository extends JpaRepository<SysUser, String> {

    SysUser findByUsernameAndPassword(String username, String password);

    SysUser findByToken(String token);

    SysUser findByUsername(String username);

    SysUser findByName(String name);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name")
    Page<PersonAndSysUser> userAndPerson(Pageable pageable);

    //下面三者根据传参的三种情况来返回对象
    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.companyName=:companyname and a.departmentName=:departmentName and a.name like :name")
    Page<PersonAndSysUser> queryPersonBy1(@Param("companyname") String companyname, @Param("departmentName") String departmentName,@Param("name") String name,Pageable pageable);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.companyName=:companyname and a.departmentName=:departmentName")
    Page<PersonAndSysUser> queryPersonBy2(@Param("companyname") String companyname, @Param("departmentName") String departmentName,Pageable pageable);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.companyName=:companyname and a.name like :name")
    Page<PersonAndSysUser> queryPersonBy3(@Param("companyname") String companyname, @Param("name") String name,Pageable pageable);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.departmentName=:departmentName and a.name like :name")
    Page<PersonAndSysUser> queryPersonBy4( @Param("departmentName") String departmentName,@Param("name") String name,Pageable pageable);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.companyName=:companyname ")
    Page<PersonAndSysUser> queryPersonBy5(@Param("companyname") String companyname,Pageable pageable);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.departmentName=:departmentName")
    Page<PersonAndSysUser> queryPersonBy6( @Param("departmentName") String departmentName,Pageable pageable);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.PersonAndSysUser(a.code,a.companyName,a.departmentName,a.name,b.username,a.sex,a.nation,a.idNo,a.mobilePhone) " +
            "from MAIN_Person a left join SysUser b on a.code=b.name where a.name like :name")
    Page<PersonAndSysUser> queryPersonBy7(@Param("name") String name,Pageable pageable);
}
